from datetime import date, datetime
from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from tack.lib.smtptools import message, send_message
from util import build_empty_row_dict, build_row_dict_for_sql, fill_dict_from_form, build_columns_values_sql, state_def, build_row_dict


class Sql:
    get_request_message = """
    SELECT subject,
           markup 
    FROM message_template
    WHERE keyword = '$keyword'
    """
    
    select_details_by_commitment_id = """
    SELECT tc.commitment_id,
           tcd.commitment_detail_id,
           tcd.requested_by,
           tcd.requester_phone,
           tcd.trans_coord_name,
           tcd.trans_coord_phone,
           tcd.org_req_name,
           tcd.org_req_phone,
           tcd.purpose,
           tcd.poc_name,
           tcd.poc_phone,
           tcd.poc_alt_phone,
           tcd.poc_smtp,
           tcd.deliver_post,
           tcd.wait,
           tcd.state_key,
           tcd.fund_cite
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
    WHERE tc.commitment_id = $commitment_id
        AND tcd.state_finish IS NULL
    """
        
    select_legs_by_commitment_id = """
    SELECT tcl.commitment_leg_id,
           tcl.start,
           tcl.finish,
           tcl.location,
           tcl.destination,
           tcl.passengers,
           tcl.cargo,
           tcl.comment
    FROM tmpo_commitment_leg tcl
    WHERE tcl.commitment_id = $commitment_id
    """

    select_drivers_by_commitment_leg_id = """
    SELECT tcld.driver_id, tmpo_driver.name
    FROM tmpo_commitment_leg_driver AS tcld INNER JOIN
         tmpo_driver ON tcld.driver_id = tmpo_driver.driver_id
    WHERE tcld.commitment_leg_id = $commitment_leg_id
    """    

    select_vehicles_by_commitment_leg_id = """
    SELECT tclv.vehicle_id, tmpo_vehicle_type.vt_description
    FROM tmpo_commitment_leg_vehicle AS tclv INNER JOIN
         tmpo_vehicle_type ON tclv.vehicle_id = tmpo_vehicle_type.vt_id
    WHERE tclv.commitment_leg_id = $commitment_leg_id
    """
    
    get_state_finish_max = """
    SELECT commitment_detail_id, commitment_id,
           state_finish, state_key
    FROM tmpo_commitment_detail
    WHERE (commitment_id = $commitment_id) AND 
    (state_finish = (SELECT MAX(state_finish) AS s_finish
                     FROM tmpo_commitment_detail AS tmpo_commitment_detail_1))
    """


def notify_setup(environ, start_response):
    commitment_id = environ['dispatcher.groups']['commitment_id']
    sql = Sql.select_details_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    rows_cd = cursor.description
    current_state_key = str(rows[0][15])
    if rows:
        output = build_row_dict(rows_cd, rows[0])
        sql = Sql.select_legs_by_commitment_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        legs = cursor.fetchall()
        legs_cd = cursor.description
        output['legs'] = []
        for leg in legs:
            leg_info = build_row_dict(legs_cd, leg)
            commitment_leg_id = leg.commitment_leg_id
            sql = Sql.select_drivers_by_commitment_leg_id
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            drivers = cursor.fetchall()
            leg_info['drivers'] = []
            for driver in drivers:
                leg_info['drivers'].append(driver.name)
            sql = Sql.select_vehicles_by_commitment_leg_id
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            vehicles = cursor.fetchall()
            leg_info['vehicles'] = []
            for vehicle in vehicles:
                leg_info['vehicles'].append(vehicle.vt_description)           
            output['legs'].append(leg_info)
    else:
        output = {}
    if len(output.keys()) == 0:
        start_response(status_codes.not_found,
                       content_headers.default_plain())
        return ['404 Not Found']
    sql = Sql.get_state_finish_max
    sql = Template(sql).substitute(commitment_id=commitment_id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    try:
        last_state_key = str(rows[0][3])
    except:
        last_state_key = '0'
    if current_state_key <> last_state_key:
        try:
            send_notify(environ, output)
            output = ['Message was sent.']
        except:
            output = ['Message was not sent.']
    else:
        output = ['Message was not sent.']
    return json_app(output)(environ, start_response)
    

def send_notify(environ, output):
    conf = environ['tack.siteconfig']
    sndr = conf['tmpo_mail_sender']
    state_key = output['state_key']
    if state_key == state_def.REQUESTED:
       poc_smtp = conf['tmpo_mail_poc'] 
    else:
        poc_smtp = output['poc_smtp']     
    cursor = environ['connection'].cursor()
    sql = Sql.get_request_message
    sql = Template(sql).substitute(keyword=state_check(state_key))
    cursor.execute(sql)
    subject, markup = cursor.fetchone()
    subs = dict(schedule_id=output['commitment_id'], 
                legs=leg_setup(environ, output))
    multiple_email_message = []
    for email in poc_smtp.split(","):
        msg = message(sndr, email, subject, 
                Template(markup).safe_substitute(subs))
        multiple_email_message.append( (email, msg) )
    send_message(sndr,
                 multiple_email_message,
                 conf['tmpo_mail_host'],
                 username=conf['tmpo_mail_user'],
                 password=conf['tmpo_mail_pass'])
                 
def leg_setup(environ, output):
    display_format = '%d %b %Y %H:%M' 
    leg_setup = ''
    for legs in range(len(output['legs'])):
        start_date = datetime.strptime(output['legs'][legs]['start'], 
        '%Y-%m-%d %H:%M:%S')
        finish_date = datetime.strptime(output['legs'][legs]['finish'], 
        '%Y-%m-%d %H:%M:%S')
        leg_setup = leg_setup + '''Start: $start, Finish: $finish, Location: $location, Destination: $destination, Passengers: $passengers 
'''
        subs = dict(start=start_date.strftime(display_format).upper() ,
                   finish=finish_date.strftime(display_format).upper() ,
                   location=output['legs'][legs]['location'],
                   destination=output['legs'][legs]['destination'],
                   passengers=output['legs'][legs]['passengers'])
        leg_setup = Template(leg_setup).safe_substitute(subs)
    return (leg_setup)
    
def state_check(state_key):
    if state_key == state_def.REQUESTED:
        return 'tmpo_appt_rem'
    elif state_key == state_def.RESOURCED:
        return 'tmpo_appt_conf'
    elif state_key == state_def.CANCELED:
        return 'tmpo_not_canc'
    else:
        return 'none'